package com.eorchis.module.exceluitls;

import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.eorchis.core.log4j.ILog4jManager;
import com.eorchis.module.exceluitls.domain.excel.ExcelCell;
import com.eorchis.module.exceluitls.domain.excel.ExcelRow;
import com.eorchis.module.exceluitls.domain.excel.ExcelSheet;

/**
 * 
 * Title: ReadExcel<br>
 * Description: excel表格读取<br>
 * Company: ORCHIS<br>
 * Copyright @ 2014 ORCHIS .All rights reserved.<br>
 * @author Max
 * @createDate 2014-5-9
 * @version $Revision: 1.1 $
 */
@Repository(value = "com.eorchis.module.exceluitls.ReadExcelUtils")
public class ReadExcelUtils {

	@Autowired
	private ILog4jManager log4jManager;

	/**
	 * 读取excel表格
	 * 
	 * @param filename
	 * @return
	 * @author Max
	 * @date 2014-5-9
	 */
	public ExcelSheet readFile(InputStream in) {

		ExcelSheet excelSheet = null;
		try {
			// 创建对Excel工作簿文件的引用
			HSSFWorkbook workbook = new HSSFWorkbook(in);
			HSSFSheet sheet = workbook.getSheetAt(0);
			List<ExcelRow> rowList = new ArrayList<ExcelRow>();
			// 读取单元格
			int lastRowNum = sheet.getLastRowNum();
			for (int i = 0; i <= lastRowNum; i++) {
				HSSFRow row = sheet.getRow(i);
				if (row == null) {
					continue;
				}
				ExcelRow excelRow = new ExcelRow();
				excelRow.setRowNum(i + 1);
				List<ExcelCell> cellList = new ArrayList<ExcelCell>();
				// 取最后一个单元格的列数字
				int lastCellNum = row.getLastCellNum();
				for (int j = 0; j < lastCellNum; j++) {
					HSSFCell cell = row.getCell(j);
					if (cell == null) {
						ExcelCell excelCell = new ExcelCell();
						excelCell.setCellNum(j + 1);
						excelCell.setRowNum(i + 1);
						excelCell.setCellValue(null);
						cellList.add(excelCell);
						continue;
					}
					ExcelCell excelCell = new ExcelCell();
					excelCell.setCellNum(j + 1);
					excelCell.setRowNum(i + 1);
					// 读取单元内容
					int type = cell.getCellType();
					switch (type) {
					case HSSFCell.CELL_TYPE_BLANK:
						excelCell.setCellValue(cell.getRichStringCellValue().toString());
						break;
					case HSSFCell.CELL_TYPE_BOOLEAN:
						excelCell.setCellValue(cell.getBooleanCellValue());
						break;
					case HSSFCell.CELL_TYPE_ERROR:
						excelCell.setCellValue(cell.getErrorCellValue());
						break;
					case HSSFCell.CELL_TYPE_FORMULA:
						excelCell.setCellValue(cell.getCellFormula());
						break;
					case HSSFCell.CELL_TYPE_NUMERIC:
						excelCell.setCellValue(cell.getNumericCellValue());
						//Double number = cell.getNumericCellValue();
//						try {
//							String dateStr = number.intValue() + "";
//							SimpleDateFormat format = new SimpleDateFormat("yyyyMMdd");
//							Date date = format.parse(dateStr);
//							excelCell.setCellValue(dateStr);
//							excelCell.setDateValue(date);
//						} catch (Exception e) {
//							excelCell.setCellValue(number.intValue());
//							excelCell.setCellDoubleValue(number);
//						}
						break;
					case HSSFCell.CELL_TYPE_STRING:
						excelCell.setCellValue(cell.toString());
						break;
					}
					cellList.add(excelCell);
				}
				excelRow.setCellList(cellList);
				rowList.add(excelRow);
			}
			excelSheet = new ExcelSheet();
			// 创建对工作表的引用。只取第一个sheet
			excelSheet.setSheetNum(1);
			excelSheet.setRowList(rowList);
		} catch (Exception e) {
			log4jManager.saveExceptionLog(ReadExcelUtils.class.getName(), "readFile", "读取excel表格错误：", e);
		} finally {
			if (in != null) {
				try {
					in.close();
				} catch (IOException e) {
					log4jManager.saveExceptionLog(ReadExcelUtils.class.getName(), "readFile", "读取excel表格时关闭流错误：", e);
				}
			}
		}
		return excelSheet;
	}

}
